package com.flyme.module.base.provider.controller;

import cn.afterturn.easypoi.entity.vo.MapExcelConstants;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.flyme.common.core.model.ResultBody;
import com.flyme.common.core.model.query.CriteriaQuery;
import com.flyme.common.core.model.query.PagerInfo;
import com.flyme.common.core.model.query.PqGrid;
import com.flyme.common.core.utils.ObjectUtils;
import com.flyme.core.base.supper.entity.EntityMap;
import com.flyme.module.base.client.dto.SysChangecertificateDto;
import com.flyme.module.base.client.dto.SysComplanDto;
import com.flyme.module.base.client.dto.SysUserDto;
import com.flyme.module.base.client.dto.SysUserexaminationDto;
import com.flyme.module.base.client.entity.*;
import com.flyme.module.base.provider.service.*;
import com.flyme.module.exam.client.entity.Exam;
import com.flyme.module.exam.client.entity.ExamOpt;
import com.flyme.module.exam.provider.service.ExamOptService;
import com.flyme.module.exam.provider.service.ExamService;
import com.flyme.module.exam.provider.service.ExamTypeService;
import com.flyme.module.file.provider.service.SysCataLogService;
import com.flyme.module.file.provider.service.SysCourseService;
import com.flyme.module.order.client.entity.Order;
import com.flyme.module.order.provider.service.OrderService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * h5
 */
@Slf4j
@Controller
@RequestMapping("/common")
public class ExportController {

    @Autowired
    private OrderService orderService;
    @Autowired
    private SysCreditbankService creditbankService;
    @Autowired
    private SysUserService userService;
    @Autowired
    private ExamService examService;
    @Autowired
    private ExamOptService examOptService;
    @Autowired
    private ExamTypeService examTypeService;
    @Autowired
    private SysCertificateService certificateService;
    @Autowired
    private SysUsercertificateService usercertificateService;
    @Autowired
    private SysComplanService complanService;
    @Autowired
    private SysCataLogService cataLogService;
    @Autowired
    private SysUsersturecordService usersturecordService;
    @Autowired
    private SysCourseService courseService;
    @Autowired
    private SysGkregisterService gkregisterService;
    @Autowired
    private SysUserexaminationService userexaminationService;
    @Autowired
    private SysExaminationService examinationService;
    @Autowired
    private SysDeptService deptService;
    @Autowired
    private SysCompanyService companyService;
    @Autowired
    private SysChangecertificateService changecertificateService;


    @GetMapping("/download")
    public String download(ModelMap modelMap, PagerInfo pagerInfo,Order order) {
        String userName= ObjectUtils.getParameter("userName");
        String mobile= ObjectUtils.getParameter("mobile");
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity orderNo = new ExcelExportEntity("订单号", "id",25);
        ExcelExportEntity orderTitle = new ExcelExportEntity("商品名称", "orderTitle",20);
        ExcelExportEntity orderEntityText = new ExcelExportEntity("消费类型", "orderEntityText",20);
        ExcelExportEntity payTypeText = new ExcelExportEntity("支付方式", "payTypeText",20);
        ExcelExportEntity isInvoice = new ExcelExportEntity("发票", "isInvoice",20);
        isInvoice.setReplace(new String[]{"已开_1","未开_2"});
        ExcelExportEntity orderAmount = new ExcelExportEntity("支付金额", "orderAmount",20);
        ExcelExportEntity payStatusText = new ExcelExportEntity("支付状态", "payStatusText",20);
        ExcelExportEntity payDate = new ExcelExportEntity("支付日期", "payDate",20);
        ExcelExportEntity returnAmount = new ExcelExportEntity("退款金额", "returnAmount", 20);
        ExcelExportEntity returnDate = new ExcelExportEntity("退款日期", "returnDate", 20);
        ExcelExportEntity reason = new ExcelExportEntity("退款原因", "reason", 20);
        ExcelExportEntity userNameField = new ExcelExportEntity("下单人", "userName",20);
        ExcelExportEntity linkPhone = new ExcelExportEntity("联系方式", "linkPhone",20);
        entity.add(orderNo);
        entity.add(orderTitle);
        entity.add(orderEntityText);
        entity.add(payTypeText);
        entity.add(isInvoice);
        entity.add(orderAmount);
        entity.add(payStatusText);
        entity.add(payDate);
        entity.add(returnAmount);
        entity.add(returnDate);
        entity.add(reason);
        entity.add(userNameField);
        entity.add(linkPhone);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        pagerInfo.setPageSize(1000);
        cq.select("o.id,orderTitle,orderEntity,orderNo,orderBody,targetId,orderNo,orderAmount,orderStatus,payStatus,payType," +
            "payDate,returnDate,returnAmount,o.reason,userId,user.avatar,user.userName,user.mobile,o.company,linkPhone,linkName,isInvoice,o.createTime");
        cq.eq("orderEntity", order.getOrderEntity());
        cq.eq("targetId", order.getTargetId());
        cq.eq("o.id", order.getId());
        cq.ge("payDate", order.getStartDate());
        cq.like("user.userName",userName);
        cq.le("payDate", order.getEndDate());
        cq.eq("o.payType",order.getPayType());
        cq.createAlias("user", "user");
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = orderService.listEntityMaps(cq);
        if (ObjectUtils.isNotEmpty(list)) {
            EntityMap total = new EntityMap();
            BigDecimal totalAmount = new BigDecimal("0");
            BigDecimal totalReturnAmount = new BigDecimal("0");
            for (EntityMap entityMap : list) {
                totalAmount = totalAmount.add(entityMap.get("orderAmount"));
                BigDecimal r = entityMap.get("returnAmount");
                if (ObjectUtils.isNotEmpty(r)) {
                    totalReturnAmount = totalReturnAmount.add(r);
                }
            }
            total.put("id", "合计");
            total.put("orderAmount", totalAmount);
            total.put("returnAmount", totalReturnAmount);
            list.add(total);
        }
        ExportParams params = new ExportParams("订单管理", "测试", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "订单管理");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }


    @GetMapping("/uservip_export")
    public String download2(ModelMap modelMap, PagerInfo pagerInfo, Order order) {
        String userName= ObjectUtils.getParameter("userName");
        String mobile= ObjectUtils.getParameter("mobile");
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity orderNo = new ExcelExportEntity("订单号", "id",25);
        ExcelExportEntity userNameField = new ExcelExportEntity("个人名称", "userName",20);
        ExcelExportEntity userMobile = new ExcelExportEntity("联系方式", "mobile",20);
        ExcelExportEntity payTypeText = new ExcelExportEntity("支付方式", "payTypeText",20);
        ExcelExportEntity orderAmount = new ExcelExportEntity("支付金额", "orderAmount",20);
        ExcelExportEntity payStatusText = new ExcelExportEntity("支付状态", "payStatusText",20);
        ExcelExportEntity payDate = new ExcelExportEntity("支付日期", "payDate",20);
        entity.add(orderNo);
        entity.add(userMobile);
        entity.add(userNameField);
        entity.add(payTypeText);
        entity.add(orderAmount);
        entity.add(payStatusText);
        entity.add(payDate);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        cq.select("o.id,orderTitle,orderEntity,orderNo,orderBody,targetId,orderNo,orderAmount,orderStatus,payStatus,payType,payDate,userId,user.avatar,user.userName,o.createTime,user.mobile");
        cq.eq("orderEntity", order.getOrderEntity());
        cq.eq("targetId", order.getTargetId());
        cq.eq("o.id", order.getId());
        cq.ge("payDate", order.getStartDate());
        cq.like("user.userName",userName);
        cq.le("payDate", order.getEndDate());
        cq.eq("o.payType",order.getPayType());
        cq.createAlias("user", "user");
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = orderService.listEntityMaps(cq);
        if (ObjectUtils.isNotEmpty(list)) {
            EntityMap total = new EntityMap();
            BigDecimal totalAmount = new BigDecimal("0");
            for (EntityMap entityMap : list) {
                totalAmount = totalAmount.add(entityMap.get("orderAmount"));
            }
            total.put("id", "合计");
            total.put("orderAmount", totalAmount);
            list.add(total);
        }
        ExportParams params = new ExportParams("个人会员订单", "个人会员订单", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "个人会员订单");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }


    @GetMapping("/download3")
    public String download3(ModelMap modelMap, PagerInfo pagerInfo, Order order) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity orderNo = new ExcelExportEntity("订单号", "id", 25);
        ExcelExportEntity company = new ExcelExportEntity("企业名称", "company", 15);
        ExcelExportEntity linkPhone = new ExcelExportEntity("联系方式", "linkPhone", 20);
        ExcelExportEntity linkName = new ExcelExportEntity("联系人", "linkName", 20);
        ExcelExportEntity targetId = new ExcelExportEntity("vip类型", "targetId");
        ExcelExportEntity payTypeText = new ExcelExportEntity("支付方式", "payTypeText");
        ExcelExportEntity orderAmount = new ExcelExportEntity("支付金额", "orderAmount");
        ExcelExportEntity hyqx = new ExcelExportEntity("会员有效期", "hyqx");
        ExcelExportEntity payDate = new ExcelExportEntity("支付日期", "payDate", 20);
        entity.add(orderNo);
        entity.add(company);
        entity.add(payTypeText);
        entity.add(linkPhone);
        entity.add(linkName);
        entity.add(targetId);
        entity.add(payTypeText);
        entity.add(orderAmount);
        entity.add(hyqx);
        entity.add(payDate);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        cq.select("o.id,orderTitle,orderEntity,orderNo,orderBody,'企业VIP' targetId,'一年' hyqx,orderNo,orderAmount,orderStatus,payStatus,payType,payDate,o.company,linkPhone,linkName,isInvoice,o.createTime");
        cq.eq("orderEntity", order.getOrderEntity());
        cq.eq("targetId", order.getTargetId());
        cq.eq("o.id", order.getId());
        cq.like("o.company", order.getCompany());
        cq.like("linkPhone", order.getLinkPhone());
        cq.like("linkName", order.getLinkName());
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = orderService.listEntityMaps(cq);
        if (ObjectUtils.isNotEmpty(list)) {
            EntityMap total = new EntityMap();
            BigDecimal totalAmount = new BigDecimal("0");
            for (EntityMap entityMap : list) {
                totalAmount = totalAmount.add(entityMap.get("orderAmount"));
            }
            total.put("id", "合计");
            total.put("orderAmount", totalAmount);
            list.add(total);
        }
        ExportParams params = new ExportParams("企业会员订单", "企业会员订单", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "企业会员订单");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }

    /**
     * 学分银行
     */
    @GetMapping("/downloadCredit")
    public String download(ModelMap modelMap, PagerInfo pagerInfo, SysUserDto user) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity username = new ExcelExportEntity("姓名", "userName",25);
        ExcelExportEntity sex = new ExcelExportEntity("性别", "sex",20);
        ExcelExportEntity cardType = new ExcelExportEntity("证件类型", "cardType",20);
        ExcelExportEntity cardNo = new ExcelExportEntity("证件号码", "cardNo",20);
        ExcelExportEntity mobile = new ExcelExportEntity("手机号码", "mobile",20);
        ExcelExportEntity studentNo = new ExcelExportEntity("学号", "studentNo",20);
        ExcelExportEntity birthday = new ExcelExportEntity("出生年月", "birthday",20);
        ExcelExportEntity nationName = new ExcelExportEntity("民族", "nationName",20);
        ExcelExportEntity nationalityName = new ExcelExportEntity("国籍", "nationalityName",20);
        ExcelExportEntity eduDegreeName = new ExcelExportEntity("教育程度", "eduDegreeName",20);
        ExcelExportEntity polStatusName = new ExcelExportEntity("政治面貌", "polStatusName",20);
        ExcelExportEntity phone = new ExcelExportEntity("固定电话", "phone",20);
        ExcelExportEntity address = new ExcelExportEntity("通讯地址", "address",20);
        ExcelExportEntity postcode = new ExcelExportEntity("邮政编码", "postcode",20);
        ExcelExportEntity email = new ExcelExportEntity("电子邮箱", "email",20);
        ExcelExportEntity areaName = new ExcelExportEntity("城市", "areaName",20);
        ExcelExportEntity company = new ExcelExportEntity("工作单位", "company",20);
        ExcelExportEntity deptName = new ExcelExportEntity("部门", "deptName",20);
        ExcelExportEntity positionName = new ExcelExportEntity("职务/岗位", "positionName",20);
        ExcelExportEntity profTitle = new ExcelExportEntity("职称", "profTitle",20);
        ExcelExportEntity trade = new ExcelExportEntity("行业类型", "trade",20);
        ExcelExportEntity qq = new ExcelExportEntity("QQ号", "qq",20);
        ExcelExportEntity credentialNum = new ExcelExportEntity("证书号码", "credentialNum",20);
        ExcelExportEntity credentialNo = new ExcelExportEntity("证书编号", "credentialNo",20);
        ExcelExportEntity credentialTime = new ExcelExportEntity("颁证日期", "credentialTime",20);
        ExcelExportEntity training = new ExcelExportEntity("培训机构", "training",20);
        ExcelExportEntity examOrg = new ExcelExportEntity("考试机构", "examOrg",20);
        ExcelExportEntity deadline = new ExcelExportEntity("有效期终止日期", "deadline",20);
        entity.add(username);
        entity.add(sex);
        entity.add(cardType);
        entity.add(cardNo);
        entity.add(mobile);
        entity.add(studentNo);
        entity.add(birthday);
        entity.add(nationName);
        entity.add(nationalityName);
        entity.add(eduDegreeName);
        entity.add(polStatusName);
        entity.add(phone);
        entity.add(address);
        entity.add(postcode);
        entity.add(email);
        entity.add(areaName);
        entity.add(company);
        entity.add(deptName);
        entity.add(positionName);
        entity.add(profTitle);
        entity.add(trade);
        entity.add(qq);
        entity.add(credentialNum);
        entity.add(credentialNo);
        entity.add(credentialTime);
        entity.add(credentialTime);
        entity.add(training);
        entity.add(examOrg);
        entity.add(deadline);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        pagerInfo.setPageSize(10000);
        cq.select("cb.*,user.userName,user.mobile,user.birthday,user.address,user.email,user.company,user.deptName,user.positionName,user.qq,area.areaName areaName," +
            "(SELECT d.dicDataTitle FROM sys_dictdata d WHERE d.id = cb.nation) nationName," +
            "(SELECT d.dicDataTitle FROM sys_dictdata d WHERE d.id = cb.nationality) nationalityName," +
            "(SELECT d.dicDataTitle FROM sys_dictdata d WHERE d.id = cb.polStatus) polStatusName," +
            "(SELECT e.enumName FROM sys_enum e WHERE e.enumCode = cb.eduDegree) eduDegreeName," +
            "CASE WHEN user.sex = 1 THEN '女' ELSE '男' END sex");
        cq.createAlias("area", "area");
        cq.createAlias("user", "user");
        cq.ne("isPass", 3);
        cq.eq("isPass", user.getIsPass());
        cq.like("user.userName", user.getUserName());
        cq.like("user.mobile", user.getMobile());
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = creditbankService.listEntityMaps(cq);
        ExportParams params = new ExportParams("学分管理", "学分管理", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "学分管理");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }


    /**
     * 学员管理
     */
    @GetMapping("/userDownload")
    public String userDownload(ModelMap modelMap, PagerInfo pagerInfo, SysUser user) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity userName = new ExcelExportEntity("学员姓名", "userName",25);
        ExcelExportEntity nickName = new ExcelExportEntity("昵称", "nickName",25);
        ExcelExportEntity idCard = new ExcelExportEntity("身份证号", "idCard",25);
        ExcelExportEntity compName = new ExcelExportEntity("所属企业", "compName",30);
        ExcelExportEntity mobile = new ExcelExportEntity("手机", "mobile",20);
        ExcelExportEntity email = new ExcelExportEntity("邮箱", "email",30);
        ExcelExportEntity sex = new ExcelExportEntity("性别", "sex");
        ExcelExportEntity status = new ExcelExportEntity("是否禁用", "status");
        ExcelExportEntity birthday = new ExcelExportEntity("出生年月", "birthday",20);
        ExcelExportEntity amount = new ExcelExportEntity("学习币余额", "amount");
        ExcelExportEntity integral = new ExcelExportEntity("积分余额", "integral");
        ExcelExportEntity vipBugTime = new ExcelExportEntity("会员购买日期", "vipBugTime",30);
        ExcelExportEntity expiryDate = new ExcelExportEntity("会员截止日期", "expiryDate",30);
        ExcelExportEntity createTime = new ExcelExportEntity("注册日期", "createTime",30);
        entity.add(userName);
        entity.add(nickName);
        entity.add(idCard);
        entity.add(compName);
        entity.add(mobile);
        entity.add(email);
        entity.add(sex);
        entity.add(status);
        entity.add(birthday);
        entity.add(amount);
        entity.add(integral);
        entity.add(vipBugTime);
        entity.add(expiryDate);
        entity.add(createTime);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        cq.select("u.id,userName,nickName,company.compName,mobile,email,v.createTime vipBugTime,v.expiryDate,birthday,age,positionName," +
            "u.idCard,u.createTime,b.amount,b.integral,CASE WHEN sex=2 THEN '男' WHEN sex=1 THEN '女' ELSE '无' END sex," +
            "CASE WHEN u.status=0 THEN '禁用' ELSE '启用' END status");
        cq.like("u.userName", user.getUserName());
        cq.like("u.mobile", user.getMobile());
        cq.eq("u.userType", user.getUserType());
        cq.gt("u.createTime", user.getStartDate());
        cq.lt("u.createTime", user.getEndDate());
        cq.createAlias("vip", "v");
        cq.createAlias("company", "company");
        cq.createAlias("balance", "b");
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = userService.listEntityMaps(cq);
        ExportParams params = new ExportParams("学员管理", "学员管理", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "学员管理");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }


    /**
     * 学员学习计划管理
     */
    @GetMapping("/userPlanDownload")
    public String userPlanDownload(ModelMap modelMap, PagerInfo pagerInfo, SysComplanDto complanDto) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity userName = new ExcelExportEntity("学员姓名", "userName",25);
        ExcelExportEntity mobile = new ExcelExportEntity("手机号", "mobile",25);
        ExcelExportEntity idCard = new ExcelExportEntity("身份证号", "idCard",30);
        ExcelExportEntity departmentTitle = new ExcelExportEntity("所属部门", "departmentTitle",20);
        ExcelExportEntity email = new ExcelExportEntity("邮箱", "email",30);
        ExcelExportEntity planName = new ExcelExportEntity("学习计划名称", "planName", 30);
        ExcelExportEntity planTime = new ExcelExportEntity("学习时长", "planTime");
        ExcelExportEntity degree = new ExcelExportEntity("完成度", "degree");
        ExcelExportEntity beginTime = new ExcelExportEntity("计划开始时间", "beginTime",20);
        ExcelExportEntity endTime = new ExcelExportEntity("计划结束时间", "endTime",20);
        ExcelExportEntity courseTime = new ExcelExportEntity("计划时常", "courseTime",30);
        entity.add(userName);
        entity.add(mobile);
        entity.add(idCard);
        entity.add(departmentTitle);
        entity.add(email);
        entity.add(planName);
        entity.add(planTime);
        entity.add(degree);
        entity.add(beginTime);
        entity.add(endTime);
        entity.add(courseTime);
        SysComplan complan = complanService.getById(complanDto.getPlanId());
        long betweenDay = DateUtil.between(DateUtil.parseDateTime(complan.getStarTime()), DateUtil.parseDateTime(complan.getEndTime()), DateUnit.DAY, false);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        cq.select("u.id", "userName", "idCard", "mobile", "'" + complan.getPlanName() + "' planName", "email", "dept.deptName departmentTitle", "'" + betweenDay + "天' planTime");
        cq.like("u.jobNo", complanDto.getJobNo());
        cq.like("u.userName", complanDto.getUserName());
        if (ObjectUtils.isNotEmpty(complanDto.getDeptIds())) {
            cq.in("u.deptId", complanDto.getDeptIds().split(","));
        }
        if (ObjectUtils.isNotEmpty(complan.getUserIds())) {
            cq.in("u.id", complan.getUserIds().split(","));
        }
        cq.eq("u.userType", "UT_STUDENT");
        cq.ge("u.hiredate", complanDto.getStartDate());
        cq.le("u.hiredate", complanDto.getEndDate());
        cq.createAlias("dept", "dept");
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = userService.listEntityMaps(cq);
        //计算学习进度
        //计算所有课程总视频数量
        Integer allNum = cataLogService.countByCourseIds(complan.getCourseIds().split(","));
        for (EntityMap em : list) {
            //计算用户该计划一共看了的视频数量
            Integer userNum = usersturecordService.countByPlan(em.get("id"), complanDto.getPlanId());
            BigDecimal userDegree = new BigDecimal(userNum).divide(new BigDecimal(allNum), 2, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal("100"));
            if (userDegree.compareTo(new BigDecimal(100)) == 1) {
                userDegree = new BigDecimal("100");
            }
            em.put("degree", userDegree + "%");
            em.put("beginTime", complan.getStarTime());
            em.put("endTime", complan.getEndTime());
            em.put("courseTime", complan.getCourseTimes() + "分钟");
        }
        ExportParams params = new ExportParams("学员学习计划", "学员学习计划", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "学员学习计划");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }


    /**
     * 学习计划课程管理
     */
    @GetMapping("/coursePlanDownload")
    public String coursePlanDownload(ModelMap modelMap,PagerInfo pagerInfo, SysComplanDto complanDto) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity planName = new ExcelExportEntity("计划名称", "planName",30);
        ExcelExportEntity courseTitle = new ExcelExportEntity("课程名称", "courseTitle",25);
        ExcelExportEntity lecturerName = new ExcelExportEntity("课程导师", "lecturerName",25);
        ExcelExportEntity courseCount = new ExcelExportEntity("视频数量", "courseCount",30);

        entity.add(planName);
        entity.add(courseTitle);
        entity.add(lecturerName);
        entity.add(courseCount);

        SysComplan complan = complanService.getById(complanDto.getPlanId());
        String[] courseId = complan.getCourseIds().split(",");
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        cq.select("'" + complan.getPlanName() + "' planName","c.courseTitle","lecturer.lecturerName","(select count(id) from sys_catalog ca where ca.courseId=c.id and parentId!=0) courseCount");
        cq.in("c.id", courseId);
        cq.like("c.courseTitle", complanDto.getUserName());
        cq.createAlias("lecturer", "lecturer");
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = courseService.listEntityMaps(cq);

        String ExportName = complan.getPlanName() + "学习计划课程";
        ExportParams params = new ExportParams(ExportName, ExportName, ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "学习计划课程");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }


    /**
     * 导入
     *
     * @param file
     */
    @RequestMapping(value = "/importExam", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importExcel(@RequestParam("file") MultipartFile file) {
        String message = "";
        EntityMap levelMap = new EntityMap();//难度
        levelMap.put("易", "LT_EASY").put("中", "LT_MEDIUM").put("难", "LT_DIFFICULTY");
        EntityMap duanMap = new EntityMap();//段位
        duanMap.put("青铜", "bronze").put("白银", "silver").put("黄金", "gold").put("铂金", "platinum").put("钻石", "diamond");
        EntityMap examTypeList = examTypeService.selectMap();
        try {
            List<Exam> exams = new ArrayList<>();
            List<ExamOpt> examopts = new ArrayList<>();
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            for (Map<Object, Object> excelExportEntity : personVoList) {
                String examTitle = String.valueOf(excelExportEntity.get("题目标题"));
                if(ObjectUtils.isEmpty(excelExportEntity.get("题目标题"))){
                    continue;
                }
                String examType = String.valueOf(excelExportEntity.get("所属纬度"));
                String level = String.valueOf(excelExportEntity.get("题目难度"));
                String duanLevel = String.valueOf(excelExportEntity.get("段位等级"));
                String answeringTime = String.valueOf(excelExportEntity.get("答题限时"));
                String targeType = String.valueOf(excelExportEntity.get("题目类型"));
                String correct = excelExportEntity.get("正确答案").toString();
                String choose1 = String.valueOf(excelExportEntity.get("A"));
                String choose2 = String.valueOf(excelExportEntity.get("B"));
                String choose3 = String.valueOf(excelExportEntity.get("C"));
                String choose4 = String.valueOf(excelExportEntity.get("D"));
                String choose5 = String.valueOf(excelExportEntity.get("E"));
                String choose6 = String.valueOf(excelExportEntity.get("F"));
                EntityMap chooseMap = new EntityMap();
                chooseMap.put("A", choose1).put("B", choose2).put("C", choose3).put("D", choose4).put("E", choose5).put("F", choose6);
                String levelCode = levelMap.get(level);
                if(ObjectUtils.isEmpty(levelCode)){
                    message = message + "," + examTitle;
                    continue;
                }
                String duanCode = duanMap.get(duanLevel);
                if(ObjectUtils.isEmpty(duanCode)){
                    message = message + "," + examTitle;
                    continue;
                }
                Long id = examTypeList.get(examType);
                if(ObjectUtils.isEmpty(id)){
                    message = message + "," + examTitle;
                    continue;
                }
                Exam exam = new Exam();
                exam.setId(IdWorker.getId());
                exam.setExamTitle(examTitle);
                exam.setExamSynopsis(examTitle);
                exam.setExamTypeId(id.intValue());
                exam.setAnsweringTime(answeringTime);
                exam.setLevel(levelCode);
                exam.setDuanLevel(duanCode);
                exam.setFrequency(0);
                exam.setExamIdStamp(exam.getId().toString() + System.currentTimeMillis());
                if(targeType.equals("选择题")){
                    exam.setTargeType(1);
                }else{
                    exam.setTargeType(2);
                }
                exams.add(exam);
                for (EntityMap.Entry<String, Object> m : chooseMap.entrySet()) {
                    if(!m.getValue().equals("null")){
                        ExamOpt examopt = new ExamOpt();
                        examopt.setExamOptionName(m.getValue().toString());
                        examopt.setExamOptionCode(m.getKey());
                        examopt.setCorrect(false);
                        if(m.getKey().equals(correct)){
                            examopt.setCorrect(true);
                        }
                        examopt.setExamId(exam.getId());
                        examopts.add(examopt);
                    }
                }
            }
            examService.saveBatch(exams);
            examOptService.saveBatch(examopts);
        }catch (Exception e){
            return ResultBody.failed("导入失败，请检查后重新导入");
        }
        if(message.length() > 0){
            return ResultBody.ok(message.substring(1)).setCode(2105);
        }
        return ResultBody.ok("导入成功");
    }



    /**
     * 导入证书
     *
     * @param file
     */
    @RequestMapping(value = "/importCertificateExcel", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importCertificateExcel(@RequestParam("file") MultipartFile file) {
        String message = "";
        try {
            List<SysUsercertificate> usercertificate = new ArrayList<>();
            List<SysUserexamination> userexaminations = new ArrayList<>();
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            EntityMap map = certificateService.selectList();
            for (Map<Object, Object> excelExportEntity : personVoList) {
                if(ObjectUtils.isEmpty(excelExportEntity.get("证书名称"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("证书code值"))){
                    continue;
                }
                String certificateCode = String.valueOf(excelExportEntity.get("证书code值"));
                String certificateNum = String.valueOf(excelExportEntity.get("证书编号"));
                String award = String.valueOf(excelExportEntity.get("颁证机构"));
                String validity = String.valueOf(excelExportEntity.get("有效期"));
                String awardTime = String.valueOf(excelExportEntity.get("颁证时间"));
                String idCard = String.valueOf(excelExportEntity.get("身份证号"));
                String examinationCode = String.valueOf(excelExportEntity.get("考试code"));
                SysUser user = userService.findByIdCard(idCard);
                if(ObjectUtils.isEmpty(user)){
                    continue;
                }
                SysCertificate sc = certificateService.findByCode(certificateCode);
                if(ObjectUtils.isEmpty(sc)){
                    continue;
                }
                SysExamination examination = examinationService.getByCode(examinationCode);
                if(ObjectUtils.isNotEmpty(examination)){
                    //导入证书时，将用户考试记录中是否颁发证书改为已颁发
                    SysUserexamination userexamination = userexaminationService.findByCode(user.getId(), examination.getId());
                    if(ObjectUtils.isNotEmpty(userexamination)){
                        userexamination.setIsAward(1);
                        userexaminations.add(userexamination);
                    }
                }
                SysUsercertificate certificate = new SysUsercertificate();
                certificate.setCertificateId(map.get(certificateCode));
                certificate.setSumperiod(0);
                certificate.setUserId(user.getId());
                certificate.setCertificateNum(certificateNum);
                certificate.setAward(award);
                certificate.setValidity(validity);
                certificate.setAwardTime(awardTime);
                certificate.setIsAffirm(0);
                certificate.setIsFull(0);
                certificate.setIdCard(idCard);
                if(sc.getIsChange().equals(1) && sc.getIsConvert().equals(0)){
                    certificate.setChangeStatus(0);
                }
                Integer num = usercertificateService.countByIdCard(idCard, certificateNum);
                if(num < 1){
                    usercertificate.add(certificate);
                }
            }
            //批量将用户考试记录改为已颁发证书
            if(userexaminations.size() > 0){
                userexaminationService.updateBatchById(userexaminations);
            }
            usercertificateService.saveBatch(usercertificate);
        }catch (Exception e){
            return ResultBody.failed("导入失败");
        }
        if(message.length() > 0){
            return ResultBody.ok(message.substring(1)).setCode(2105);
        }
        return ResultBody.ok("导入成功");
    }


    /**
     * 导入员工
     *
     * @param file
     */
    @RequestMapping(value = "/importStaffExcel", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importStaffExcel(@RequestParam("file") MultipartFile file, Long companyId ) {
        try {
            EntityMap deptMap = deptService.selectDept();
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            for (Map<Object, Object> excelExportEntity : personVoList) {
                if(ObjectUtils.isEmpty(excelExportEntity.get("电话"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("姓名"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(companyId)){
                    continue;
                }
                String jobNo = String.valueOf(excelExportEntity.get("工号"));
                String userName = String.valueOf(excelExportEntity.get("姓名"));
                String nickName = String.valueOf(excelExportEntity.get("昵称"));
                String mobile = String.valueOf(excelExportEntity.get("电话"));
                String idCard = String.valueOf(excelExportEntity.get("身份证号"));
                String dept = String.valueOf(excelExportEntity.get("部门"));
                Long deptId = deptMap.get(dept);
                SysUserDto user = new SysUserDto();
                user.setJobNo(jobNo);
                user.setUserName(userName);
                user.setNickName(nickName);
                user.setMobile(mobile);
                user.setIdCard(idCard);
                if(ObjectUtils.isNotEmpty(deptId)){
                    user.setDeptId(deptId.toString());
                }
                boolean flag = userService.addStaffs(user, companyId);
                if(!flag){
                    return ResultBody.failed("导入失败");
                }
            }
        }catch (Exception e){
            return ResultBody.failed("导入失败");
        }
        return ResultBody.ok("导入成功");
    }


    /**
     * 导入国开学籍
     *
     * @param file
     */
    @RequestMapping(value = "/importGkExcel", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importGkExcel(@RequestParam("file") MultipartFile file) {
        try {
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            List<SysUser> userList = new ArrayList<>();
            for (Map<Object, Object> excelExportEntity : personVoList) {
                if(ObjectUtils.isEmpty(excelExportEntity.get("身份证号"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("国开学籍号"))){
                    continue;
                }
                String idCard = String.valueOf(excelExportEntity.get("身份证号"));
                String schoolNum = String.valueOf(excelExportEntity.get("国开学籍号"));
                SysUser user = userService.findByIdCard(idCard);
                user.setSchoolNum(schoolNum);
                user.setSchoolRegister("2");
                userList.add(user);
            }
            userService.updateBatchById(userList);
        }catch (Exception e){
            return ResultBody.failed("导入失败");
        }
        return ResultBody.ok("导入成功");
    }

    /**
     * 导入学员考试记录
     */
    @RequestMapping(value = "/importExaminationExcel", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importExaminationExcel(@RequestParam("file") MultipartFile file) {
        try {
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            List<SysUserexamination> examinationList = new ArrayList<>();
            for (Map<Object, Object> excelExportEntity : personVoList) {
                if(ObjectUtils.isEmpty(excelExportEntity.get("身份证号"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("证书code"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("考试安排code"))){
                    continue;
                }
                String idCard = String.valueOf(excelExportEntity.get("身份证号"));
                String certificateCode = String.valueOf(excelExportEntity.get("证书code"));
                String payTime = String.valueOf(excelExportEntity.get("交费时间"));
                String examinationCode = String.valueOf(excelExportEntity.get("考试安排code"));
                SysExamination examination = examinationService.getByCode(examinationCode);
                SysCertificate certificate = certificateService.findByCode(certificateCode);
                SysUser user = userService.findByIdCard(idCard);
                if(ObjectUtils.isNotEmpty(certificate) && ObjectUtils.isNotEmpty(user) && ObjectUtils.isNotEmpty(examination)){
                    SysUserexamination userexamination = new SysUserexamination();
                    userexamination.setUserId(user.getId());
                    userexamination.setExamStatus(1);
                    userexamination.setExamType(0);
                    userexamination.setCertificateId(certificate.getId());
                    userexamination.setPayTime(payTime);
                    userexamination.setExaminationId(examination.getId());
                    examinationList.add(userexamination);
                }
            }
            userexaminationService.saveBatch(examinationList);
        }catch (Exception e){
            return ResultBody.failed("导入失败");
        }
        return ResultBody.ok("导入成功");
    }

    /**
     * 导入学员考试成绩
     */
    @RequestMapping(value = "/importExaminationCJExcel", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importExaminationCJExcel(@RequestParam("file") MultipartFile file) {
        try {
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            List<SysUserexamination> examinationList = new ArrayList<>();
            List<SysUsercertificate> usercertificateList = new ArrayList<>();
            for (Map<Object, Object> excelExportEntity : personVoList) {
                if(ObjectUtils.isEmpty(excelExportEntity.get("身份证号"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("成绩"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("考试安排code"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("学员考试编号"))){
                    continue;
                }
                String idCard = String.valueOf(excelExportEntity.get("身份证号"));
                String qualifiedString = String.valueOf(excelExportEntity.get("成绩"));
                String examinationCode = String.valueOf(excelExportEntity.get("考试安排code"));
                String examNum = String.valueOf(excelExportEntity.get("学员考试编号"));
                SysExamination examination = examinationService.getByCode(examinationCode);
                CriteriaQuery<SysUserexamination> qw = new CriteriaQuery();
                qw.eq("examNum", examNum);
                SysUserexamination userexamination = userexaminationService.getOne(qw, false);
                if(ObjectUtils.isEmpty(userexamination)){
                    continue;
                }
                Integer qualified = 0;
                if(qualifiedString.equals("合格")){
                    qualified = 1;
                }
                userexamination.setExamStatus(2);
                userexamination.setQualified(qualified);
                //该考试为证书认定
                if(examination.getExamType().equals(1)){
                    SysUsercertificate usercertificate = new SysUsercertificate();
                    usercertificate.setId(userexamination.getUsercertificateId());
                    usercertificate.setChangeStatus(0);
                    usercertificateList.add(usercertificate);
                }else{
                    if(qualifiedString.equals("合格")){
                        userexamination.setIsAward(0);
                    }
                }
                examinationList.add(userexamination);
            }
            if(usercertificateList.size() > 0){
                usercertificateService.updateBatchById(usercertificateList);
            }
            if(examinationList.size() > 0){
                userexaminationService.updateBatchById(examinationList);
            }
        }catch (Exception e){
            System.out.println(e.getMessage());
            return ResultBody.failed("导入失败");
        }
        return ResultBody.ok("导入成功");
    }

    /**
     * 导出企业信息
     */
    @GetMapping("/companyExport")
    public String companyExport(ModelMap modelMap, SysCompany company) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity compName = new ExcelExportEntity("企业名称", "compName",30);
        ExcelExportEntity status = new ExcelExportEntity("企业状态", "status",25);
        ExcelExportEntity compTel = new ExcelExportEntity("联系方式", "compTel",25);
        ExcelExportEntity linkMan = new ExcelExportEntity("联系人", "linkMan",30);
        ExcelExportEntity compEmail = new ExcelExportEntity("企业邮箱", "compEmail",30);
        ExcelExportEntity address = new ExcelExportEntity("企业地址", "address",30);
        ExcelExportEntity staffNum = new ExcelExportEntity("可添加员工数量", "staffNum",30);
        ExcelExportEntity createTime = new ExcelExportEntity("VIP购买日期", "createTime",30);
        ExcelExportEntity expiryDate = new ExcelExportEntity("VIP截止日期", "expiryDate",30);

        entity.add(compName);
        entity.add(status);
        entity.add(compTel);
        entity.add(linkMan);
        entity.add(compEmail);
        entity.add(address);
        entity.add(staffNum);
        entity.add(createTime);
        entity.add(expiryDate);

        CriteriaQuery cq = new CriteriaQuery<EntityMap>();
        cq.select("c.id", "c.compName", "if(c.status = 0,'禁用','启用') as status ", "c.compEmail", "c.address", "v.createTime", "c.compTel", "c.compLogo", "c.linkMan", "c.staffNum",
                "CASE WHEN c.status=0 THEN c.befVipTime ELSE v.expiryDate END expiryDate");
        cq.createAlias("vip", "v");
        cq.like("compName", company.getCompName());
        cq.like("compTel", company.getCompTel());
        cq.like("linkMan", company.getLinkMan());
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = companyService.listEntityMaps(cq);

        ExportParams params = new ExportParams("企业信息", "企业信息", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "企业信息");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }

    /**
     * 导入转换证明
     *
     * @param file
     */
    @RequestMapping(value = "/importProve", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importProve(@RequestParam("file") MultipartFile file) {
        try {
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            List<SysChangecertificate> proveList = new ArrayList<>();
            List<SysUsercertificate> certificateList = new ArrayList<>();
            for (Map<Object, Object> excelExportEntity : personVoList) {
                if(ObjectUtils.isEmpty(excelExportEntity.get("身份证号"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("审核状态"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("有效期"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("用户证书编号"))){
                    continue;
                }
                String validityTime = String.valueOf(excelExportEntity.get("有效期"));
                String idCard = String.valueOf(excelExportEntity.get("身份证号"));
                String auditState = String.valueOf(excelExportEntity.get("审核状态"));
                String convertCertHtml = String.valueOf(excelExportEntity.get("证书链接"));
                String reason = String.valueOf(excelExportEntity.get("备注"));
                String certificateNum = String.valueOf(excelExportEntity.get("用户证书编号"));
                SysChangecertificate change = changecertificateService.findByIdCard(idCard, certificateNum);
                if(auditState.equals("成功")){
                    change.setConvertCertHtml(convertCertHtml);
                    change.setAuditState("2");
                    change.setValidityTime(DateUtil.parseDateTime(validityTime + " 00:00:00"));
                    change.setIsUse(0);
                    SysUsercertificate certificate = new SysUsercertificate();
                    certificate.setId(change.getSysUsercertificateId());
                    certificate.setChangeStatus(2);
                    certificateList.add(certificate);
                }else{
                    change.setReason(reason);
                    change.setAuditState("3");
                }
                proveList.add(change);
            }
            if(certificateList.size() > 0){
                usercertificateService.updateBatchById(certificateList);
            }
            changecertificateService.updateBatchById(proveList);
        }catch (Exception e){
            return ResultBody.failed("导入失败");
        }
        return ResultBody.ok("导入成功");
    }

    /**
     * 导出转换证明申请
     */
    @GetMapping("/exportChange")
    public String exportChange(ModelMap modelMap, SysChangecertificateDto scc) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity userName = new ExcelExportEntity("用户名", "userName", 20);
        ExcelExportEntity idCard = new ExcelExportEntity("身份证号", "idCard", 20);
        ExcelExportEntity schoolNum = new ExcelExportEntity("国开学籍号", "schoolNum", 20);
        ExcelExportEntity auditStateName = new ExcelExportEntity("审核状态", "auditStateName", 20);
        ExcelExportEntity mobile = new ExcelExportEntity("手机号", "mobile", 20);
        ExcelExportEntity certificateName = new ExcelExportEntity("持有证书", "certificateName", 20);
        ExcelExportEntity certificateNum = new ExcelExportEntity("证书编号", "certificateNum", 20);
        ExcelExportEntity validity = new ExcelExportEntity("有效期", "validity", 20);
        ExcelExportEntity createTime = new ExcelExportEntity("申请转换时间", "createTime", 20);

        entity.add(userName);
        entity.add(idCard);
        entity.add(schoolNum);
        entity.add(auditStateName);
        entity.add(mobile);
        entity.add(certificateName);
        entity.add(certificateNum);
        entity.add(validity);
        entity.add(createTime);

        PagerInfo pagerInfo = new PagerInfo();
        pagerInfo.setPageSize(10000);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        cq.select("*, users.userName,users.idCard,users.schoolNum,users.mobile,syscertificate.certificateName," +
                "certificate.certificateNum,certificate.award,certificate.createTime cuTime,certificate.validity," +
                "case when auditState = '1' then '待审核' when auditState = '2' then '审核通过' else '审核拒绝' end as auditStateName");
        cq.eq("auditState", scc.getAuditState());
        cq.like("users.userName", scc.getUserName());
        cq.createAlias("users", "users");
        cq.createAlias("certificate", "certificate");
        cq.createAlias("syscertificate", "syscertificate");
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = changecertificateService.listEntityMaps(cq);

        ExportParams params = new ExportParams("转换证明申请", "转换证明申请", ExcelType.XSSF);
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "转换证明申请");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }

    /**
     * 导入转换证明使用记录
     *
     * @param file
     */
    @RequestMapping(value = "/importUserRecord", method = RequestMethod.POST)
    @ResponseBody
    public ResultBody importUserRecord(@RequestParam("file") MultipartFile file) {
        try {
            List<Map<Object, Object>> personVoList = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, new ImportParams());
            List<SysUsercertificate> certificateList = new ArrayList<>();
            List<SysChangecertificate> changeList = new ArrayList<>();
            for (Map<Object, Object> excelExportEntity : personVoList) {
                if(ObjectUtils.isEmpty(excelExportEntity.get("身份证号"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("用户证书编号"))){
                    continue;
                }
                if(ObjectUtils.isEmpty(excelExportEntity.get("证明状态"))){
                    continue;
                }
                String idCard = String.valueOf(excelExportEntity.get("身份证号"));
                String certificateNum = String.valueOf(excelExportEntity.get("用户证书编号"));
                String changeStatus = String.valueOf(excelExportEntity.get("证明状态"));
                String useTime = String.valueOf(excelExportEntity.get("使用时间"));
                SysUsercertificate euc = usercertificateService.findByIdCard(idCard, certificateNum);
                if(ObjectUtils.isEmpty(euc)){
                    continue;
                }
                SysChangecertificate change = changecertificateService.findByIdCard2(idCard, certificateNum, 2);
                Integer score = certificateService.getById(euc.getCertificateId()).getScore();
                if(changeStatus.equals("转换成功")){
                    euc.setChangeStatus(3);
                    euc.setCreditScore(String.valueOf(score));
                    euc.setUseTime(useTime);
                    if(ObjectUtils.isNotEmpty(change)){
                        change.setIsUse(1);
                        change.setUseTime(useTime);
                        changeList.add(change);
                    }
                }else{
                    euc.setChangeStatus(4);
                }
                certificateList.add(euc);
            }
            if(certificateList.size() > 0){
                usercertificateService.updateBatchById(certificateList);
            }
            if(changeList.size() > 0){
                changecertificateService.updateBatchById(changeList);
            }
        }catch (Exception e){
            return ResultBody.failed("导入失败");
        }
        return ResultBody.ok("导入成功");
    }

    /**
     * 导出学员考试成绩
     */
    @GetMapping("/exportExamination")
    public String exportExamination(ModelMap modelMap, SysUserexaminationDto userexamination) {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        ExcelExportEntity userName = new ExcelExportEntity("学员姓名", "userName", 20);
        ExcelExportEntity idCard = new ExcelExportEntity("身份证号", "idCard", 20);
        ExcelExportEntity examinationCode = new ExcelExportEntity("考试安排code", "examinationCode", 20);
        ExcelExportEntity qualified = new ExcelExportEntity("成绩", "qualified", 20);
        ExcelExportEntity examNum = new ExcelExportEntity("学员考试编号", "examNum", 40);
        ExcelExportEntity examName = new ExcelExportEntity("考试名称", "examName", 20);

        entity.add(userName);
        entity.add(idCard);
        entity.add(examinationCode);
        entity.add(qualified);
        entity.add(examNum);
        entity.add(examName);

        PagerInfo pagerInfo = new PagerInfo();
        pagerInfo.setPageSize(100000);
        CriteriaQuery cq = new CriteriaQuery<EntityMap>(pagerInfo);
        cq.select("u.examNum,user.userName,user.idCard,exam.examName,exam.examinationCode," +
                "(CASE WHEN u.qualified=1 THEN '合格' WHEN u.qualified=0 THEN '不合格' ELSE 'qi' END) qualified");
        cq.like("user.userName", userexamination.getUserName());
        cq.like("user.idCard", userexamination.getIdCard());
        cq.like("exam.examName", userexamination.getExamName());
        if(!userexamination.getExamStatus().equals(100)){
            cq.eq("u.examStatus", userexamination.getExamStatus());
        }
        if(!userexamination.getExamType().equals(100)){
            cq.eq("exam.examType", userexamination.getExamType());
        }
        cq.ne("examStatus", 0);
        cq.ne("examStatus", 3);
        cq.createAlias("user", "user");
        cq.createAlias("exam", "exam");
        cq.createAlias("certificate", "certificate");
        cq.createAlias("usercertificate", "usercertificate");
        EntityMap.setEnumConvertInterceptor(new Order());
        List<EntityMap> list = userexaminationService.listEntityMaps(cq);

        ExportParams params = new ExportParams();
        params.setType(ExcelType.XSSF);
        params.setSheetName("学员考试成绩");
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, "学员考试成绩");
        return MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW;
    }

}
